/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sbmart.smartserver.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sbmart.object.*;
import sbmart.smartserver.cache.ConnectionManager;

/**
 *
 * @author albert
 */
public class ServerTrsSalesService {

    private static ServerTrsSalesService instance;
    public static final Logger LOG = LoggerFactory.getLogger("ClientTrsSales");

    private ServerTrsSalesService() {
    }

    public static ServerTrsSalesService instance() {
        if (instance == null) {
            instance = new ServerTrsSalesService();
        }
        return instance;
    }

    public synchronized Connection putTrsSalesList(Header header, TrsSalesList trs, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_trs_sales_mst = serverConn.prepareCall(ServerProcedures.put_trs_sales_mst);
        CallableStatement call_put_trs_sales_detail = serverConn.prepareCall(ServerProcedures.put_trs_sales_detail);
        CallableStatement call_put_trs_sales_item = serverConn.prepareCall(ServerProcedures.put_trs_sales_item);
        CallableStatement call_put_trs_sales_rekap = serverConn.prepareCall(ServerProcedures.put_trs_sales_rekap);

        // Loop for TrsSalesMst
        for (TrsSalesMst mst : trs.getSalesMst()) {
            call_put_trs_sales_mst.setString("trans_num", mst.getTransNum());
//                LOG.debug("TrsSalesMst trans_num: " + mst.getTransNum());
            Calendar cal = mst.getTransDate();
            if (cal != null) {
                java.sql.Timestamp date = new java.sql.Timestamp(cal.getTimeInMillis());
                call_put_trs_sales_mst.setTimestamp("trans_date", date);
            } else {
                call_put_trs_sales_mst.setTimestamp("trans_date", null);
            }
            call_put_trs_sales_mst.setInt("branch_id", mst.getBranchId());
            call_put_trs_sales_mst.setByte("media_bayar", mst.getMediaBayar());
            call_put_trs_sales_mst.setByte("card_id", mst.getCardId());
            call_put_trs_sales_mst.setFloat("subtotal", mst.getSubtotal());
            call_put_trs_sales_mst.setFloat("total", mst.getTotal());
            call_put_trs_sales_mst.setFloat("cash", mst.getCash());
            call_put_trs_sales_mst.setFloat("sumbangan", mst.getSumbangan());
            call_put_trs_sales_mst.setFloat("voucher", mst.getVoucher());
            call_put_trs_sales_mst.setInt("rekap_id", mst.getRekapId());
            call_put_trs_sales_mst.setString("display_num", mst.getDisplayNum());
            call_put_trs_sales_mst.setFloat("bayar_tunai", mst.getBayarTunai());
            call_put_trs_sales_mst.setFloat("tarik_tunai", mst.getTarikTunai());
            call_put_trs_sales_mst.setFloat("bayar_debit", mst.getBayarDebit());

            call_put_trs_sales_mst.registerOutParameter(1, Types.INTEGER);
            call_put_trs_sales_mst.execute();
            int identity = call_put_trs_sales_mst.getInt(1);

            if (identity != 0) {
                for (TrsSalesDetail detail : mst.getDetails()) {
                    call_put_trs_sales_detail.setInt("sales_id", identity);
                    call_put_trs_sales_detail.setInt("item_id", detail.getItemId());
                    call_put_trs_sales_detail.setFloat("quantity", detail.getQuantity());
                    call_put_trs_sales_detail.setFloat("price", detail.getPrice());
                    call_put_trs_sales_detail.setFloat("hpp", detail.getHpp());
                    call_put_trs_sales_detail.setInt("disc_id", detail.getDiscId());
                    call_put_trs_sales_detail.setInt("hadiah_id", detail.getHadiahId());
                    call_put_trs_sales_detail.setFloat("nilai_promo", detail.getNilaiPromo());
                    call_put_trs_sales_detail.setFloat("harga_promo", detail.getHargaPromo());
                    call_put_trs_sales_detail.setLong("inq_id", detail.getInq_id());
                    call_put_trs_sales_detail.execute();
                }
            }
        }


        // Loop for TrsSalesItem
        int index = 1;
        for (TrsSalesItem item : trs.getSalesItems()) {
            Calendar cal = item.getTransDate();
            if (cal != null) {
                java.sql.Timestamp date = new java.sql.Timestamp(cal.getTimeInMillis());
                call_put_trs_sales_item.setTimestamp("trans_date", date);
            } else {
                call_put_trs_sales_item.setTimestamp("trans_date", null);
            }
            call_put_trs_sales_item.setInt("branch_id", item.getBranchId());
            call_put_trs_sales_item.setInt("rekap_id", item.getRekapId());
            call_put_trs_sales_item.setInt("item_id", item.getItemId());
            call_put_trs_sales_item.setInt("row_id", index++);
            call_put_trs_sales_item.setInt("sales_type", item.getSalesType());
            call_put_trs_sales_item.setDouble("quantity", item.getQuantity());
            call_put_trs_sales_item.setDouble("price", item.getPrice());
            call_put_trs_sales_item.setDouble("discount", item.getDiscount());
            call_put_trs_sales_item.setDouble("discount_member", item.getDiscountMember());
            call_put_trs_sales_item.setDouble("amount", item.getAmount());
            call_put_trs_sales_item.setDouble("tax", item.getTax());
            call_put_trs_sales_item.setDouble("hpp", item.getHpp());
            call_put_trs_sales_item.setInt("vendor_id", item.getVendorId());

            call_put_trs_sales_item.registerOutParameter(1, Types.INTEGER);
            call_put_trs_sales_item.execute();
            int item_id = call_put_trs_sales_item.getInt(1);
            if(item_id > 0){
                LOG.debug("[DELETE-INSERT]\n branch_id:");
            }
        }


        // Loop for TrsSalesRekap
        for (TrsSalesRekap rekap : trs.getSalesRekaps()) {
            call_put_trs_sales_rekap.setInt("rekap_id", rekap.getRekapId());
            call_put_trs_sales_rekap.setInt("branch_id", rekap.getBranchId());
            Calendar cal1 = rekap.getTransDate();
            if (cal1 != null) {
                java.sql.Timestamp date = new java.sql.Timestamp(cal1.getTimeInMillis());
                call_put_trs_sales_rekap.setTimestamp("trans_date", date);
            } else {
                call_put_trs_sales_rekap.setTimestamp("trans_date", null);
            }

            Calendar cal2 = rekap.getCloseDate();
            if (cal1 != null) {
                java.sql.Timestamp date = new java.sql.Timestamp(cal2.getTimeInMillis());
                call_put_trs_sales_rekap.setTimestamp("close_date", date);
            } else {
                call_put_trs_sales_rekap.setTimestamp("close_date", null);
            }
            call_put_trs_sales_rekap.setString("users_id", rekap.getUsersId());
            call_put_trs_sales_rekap.setFloat("initial", rekap.getInitial());
            call_put_trs_sales_rekap.setFloat("cash", rekap.getCash());
            call_put_trs_sales_rekap.setFloat("bank", rekap.getBank());
            call_put_trs_sales_rekap.setFloat("retur", rekap.getRetur());
            call_put_trs_sales_rekap.setFloat("discount", rekap.getDiscount());
            call_put_trs_sales_rekap.setFloat("rent_received", rekap.getRentReceived());
            call_put_trs_sales_rekap.setFloat("saving", rekap.getSaving());
            call_put_trs_sales_rekap.setFloat("variant", rekap.getVariant());
            call_put_trs_sales_rekap.setFloat("contribution", rekap.getContribution());
            call_put_trs_sales_rekap.setFloat("voucher", rekap.getVoucher());
            call_put_trs_sales_rekap.setFloat("cash_received", rekap.getCashReceived());
            call_put_trs_sales_rekap.setFloat("tax", rekap.getTax());
            call_put_trs_sales_rekap.setFloat("margin", rekap.getMargin());
            call_put_trs_sales_rekap.setInt("custumer", rekap.getCustumer());
            call_put_trs_sales_rekap.setString("insert_log", rekap.getInsertLog());
            call_put_trs_sales_rekap.setString("edit_log", rekap.getEditLog());
            call_put_trs_sales_rekap.setFloat("hpp", rekap.getHpp());
            call_put_trs_sales_rekap.setFloat("tarik_tunai", rekap.getTarikTunai());
            call_put_trs_sales_rekap.setFloat("debit", rekap.getDebit());
            call_put_trs_sales_rekap.setFloat("kupon", rekap.getKupon());
            call_put_trs_sales_rekap.setFloat("sales_net", rekap.getSalesNet());
            call_put_trs_sales_rekap.setFloat("retur_net", rekap.getReturNet());
            call_put_trs_sales_rekap.setFloat("voucher_amount", rekap.getVoucherAmount());
            call_put_trs_sales_rekap.setFloat("claim", rekap.getClaim());
            call_put_trs_sales_rekap.setFloat("bayar_variant", rekap.getBayarVariant());
            call_put_trs_sales_rekap.setFloat("bayar_mitra", rekap.getBayarMitra());
            call_put_trs_sales_rekap.setFloat("online", rekap.getOnline());
            call_put_trs_sales_rekap.setFloat("cash_in", rekap.getCashIn());
            call_put_trs_sales_rekap.setFloat("cash_out", rekap.getCashOut());

            call_put_trs_sales_rekap.registerOutParameter(1, Types.INTEGER);
            call_put_trs_sales_rekap.execute();
            int ret = call_put_trs_sales_rekap.getInt(1);
        }

        return serverConn;
    }

    public synchronized Connection putTrsAdjustGerai(Header header, List<TrsAdjustGeraiMst> list, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_trs_adjust_gerai_mst = serverConn.prepareCall(ServerProcedures.put_trs_adjust_gerai_mst);
        CallableStatement call_put_trs_adjust_gerai_detail = serverConn.prepareCall(ServerProcedures.put_trs_adjust_gerai_detail);

        for (TrsAdjustGeraiMst trs : list) {
            call_put_trs_adjust_gerai_mst.setString("trans_num", trs.getTransNum());
            call_put_trs_adjust_gerai_mst.setInt("jenis", trs.getJenis());

            if (trs.getTransDate() != null) {
                call_put_trs_adjust_gerai_mst.setTimestamp("trans_date", new java.sql.Timestamp(trs.getTransDate().getTimeInMillis()));
            } else {
                call_put_trs_adjust_gerai_mst.setTimestamp("trans_date", null);
            }

            call_put_trs_adjust_gerai_mst.setShort("branch_id", trs.getBranchId());
            call_put_trs_adjust_gerai_mst.setString("notes", trs.getNotes());
            call_put_trs_adjust_gerai_mst.setString("users_id", trs.getUsersId());
            call_put_trs_adjust_gerai_mst.setByte("status_id", trs.getStatusId());
            call_put_trs_adjust_gerai_mst.setByte("adj_type", trs.getAdjType());
            call_put_trs_adjust_gerai_mst.setString("insert_log", trs.getInsertLog());
            call_put_trs_adjust_gerai_mst.setString("edit_log", trs.getEditLog());

            call_put_trs_adjust_gerai_mst.registerOutParameter(1, Types.INTEGER);
            call_put_trs_adjust_gerai_mst.execute();
            int adjust_id = call_put_trs_adjust_gerai_mst.getInt(1);

            if (adjust_id != 0) {
                List<TrsAdjustGeraiDetail> details = trs.getDetails();
                for (TrsAdjustGeraiDetail detail : details) {
                    call_put_trs_adjust_gerai_detail.setInt("adj_id", adjust_id);
                    call_put_trs_adjust_gerai_detail.setInt("item_id", detail.getItemId());
                    call_put_trs_adjust_gerai_detail.setInt("conv_id", detail.getConvId());
                    call_put_trs_adjust_gerai_detail.setFloat("quantity", detail.getQuantity());
                    call_put_trs_adjust_gerai_detail.setFloat("stok_system", detail.getStokSystem());
                    call_put_trs_adjust_gerai_detail.setFloat("conversion", detail.getConversion());
                    call_put_trs_adjust_gerai_detail.setShort("alasan_id", detail.getAlasanId());
                    call_put_trs_adjust_gerai_detail.setFloat("price", detail.getPrice());
                    call_put_trs_adjust_gerai_detail.setFloat("stok_value", detail.getStokValue());
                    call_put_trs_adjust_gerai_detail.setFloat("range_value", detail.getRangeValue());
                    call_put_trs_adjust_gerai_detail.execute();
                }
            } else {
                LOG.debug("saveTrsAdjustGerai: set status is delivered on gerai");
            }
        }
        return serverConn;
    }

    
    public synchronized Connection putMstItemStok(Header header, List<MstItemStock> data, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_mst_item_stok = serverConn.prepareCall(ServerProcedures.put_mst_item_stok);
        for (MstItemStock mst : data) {
            call_put_mst_item_stok.setInt("stok_id", mst.getStokId());
            call_put_mst_item_stok.setInt("item_id", mst.getItemId());
            call_put_mst_item_stok.setShort("branch_id", mst.getBranchId());
            call_put_mst_item_stok.setFloat("stok", mst.getStok());
            call_put_mst_item_stok.setString("insert_log", mst.getInsertLog());
            call_put_mst_item_stok.setString("edit_log", mst.getEditLog());
            call_put_mst_item_stok.addBatch();
        }
        call_put_mst_item_stok.executeBatch();
        return serverConn;

    }

    public synchronized Connection putInvItemBalance(Header header, List<InvItemBalance> list, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_inv_item_balance = serverConn.prepareCall(ServerProcedures.put_inv_item_balance);
        for (InvItemBalance inv : list) {
            call_put_inv_item_balance.setShort("branch_id", inv.getBranchId());
            call_put_inv_item_balance.setShort("storage_id", inv.getStorageId());
            call_put_inv_item_balance.setInt("item_id", inv.getItemId());

            if (inv.getBalanceDate() != null) {
                call_put_inv_item_balance.setTimestamp("balance_date", new java.sql.Timestamp(inv.getBalanceDate().getTimeInMillis()));
            } else {
                call_put_inv_item_balance.setTimestamp("balance_date", null);
            }
            call_put_inv_item_balance.setFloat("balance", inv.getBalance());
            call_put_inv_item_balance.addBatch();
        }
        call_put_inv_item_balance.executeBatch();
        return serverConn;
    }

    public synchronized Connection putTrsRentPayment(Header header, List<TrsRentPayment> list, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_trs_rent_payment = serverConn.prepareCall(ServerProcedures.put_trs_rent_payment);
        for (TrsRentPayment trs : list) {
            call_put_trs_rent_payment.setInt("branch_id", trs.getBranchId());
            call_put_trs_rent_payment.setInt("pay_id", trs.getPayId());
            call_put_trs_rent_payment.setInt("rekap_id", trs.getRekapId());
            call_put_trs_rent_payment.setShort("relation_id", trs.getRelationId());
            call_put_trs_rent_payment.setFloat("amount", trs.getAmount());
            call_put_trs_rent_payment.setString("insert_log", trs.getInsertLog());
            call_put_trs_rent_payment.setString("edit_log", trs.getEditLog());
            call_put_trs_rent_payment.setInt("rent_id", trs.getRentId());

            if (trs.getTransDate() != null) {
                call_put_trs_rent_payment.setTimestamp("trans_date", new java.sql.Timestamp(trs.getTransDate().getTimeInMillis()));
            } else {
                call_put_trs_rent_payment.setTimestamp("trans_date", null);
            }

            if (trs.getStartDate() != null) {
                call_put_trs_rent_payment.setTimestamp("start_date", new java.sql.Timestamp(trs.getStartDate().getTimeInMillis()));
            } else {
                call_put_trs_rent_payment.setTimestamp("start_date", null);
            }

            if (trs.getEndDate() != null) {
                call_put_trs_rent_payment.setTimestamp("end_date", new java.sql.Timestamp(trs.getEndDate().getTimeInMillis()));
            } else {
                call_put_trs_rent_payment.setTimestamp("end_date", null);
            }
            call_put_trs_rent_payment.addBatch();
        }
        call_put_trs_rent_payment.executeBatch();
        return serverConn;
    }

    public synchronized Connection putTrsSalesVoucher(Header header, List<TrsSalesVoucher> list, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_trs_sales_voucher = serverConn.prepareCall(ServerProcedures.put_trs_sales_voucher);
        for (TrsSalesVoucher trs : list) {
            call_put_trs_sales_voucher.setInt("branch_id", trs.getBranchId());
            call_put_trs_sales_voucher.setInt("voucher_id", trs.getVoucherId());
            call_put_trs_sales_voucher.setInt("sales_id", trs.getSalesId());

            if (trs.getTransDate() != null) {
                call_put_trs_sales_voucher.setTimestamp("trans_date", new java.sql.Timestamp(trs.getTransDate().getTimeInMillis()));
            }else{
                call_put_trs_sales_voucher.setTimestamp("trans_date", null);
            }
            call_put_trs_sales_voucher.setFloat("amount", trs.getAmount());
            call_put_trs_sales_voucher.setString("voucher_code", trs.getVoucherCode());
            call_put_trs_sales_voucher.setString("insert_log", trs.getInsertLog());
            call_put_trs_sales_voucher.setString("edit_log", trs.getEditLog());
            call_put_trs_sales_voucher.setInt("jenis", trs.getJenis());
            call_put_trs_sales_voucher.addBatch();
        }
        call_put_trs_sales_voucher.executeBatch();
        return serverConn;
    }


    public synchronized Connection putTrsSalesRemittance(Header header, List<TrsSalesRemittance> list, Connection serverConn) throws SQLException {
        LOG.debug("[PUT]");
        CallableStatement call_put_trs_sales_remittance = serverConn.prepareCall(ServerProcedures.put_trs_sales_remittance);
        for (TrsSalesRemittance trs : list) {
            call_put_trs_sales_remittance.setInt("sales_remittance_id", trs.getBranchId());
            call_put_trs_sales_remittance.setString("trans_code", trs.getTransCode());
            call_put_trs_sales_remittance.setShort("trans_type", trs.getTransType());

            if (trs.getTransDate() != null) {
                call_put_trs_sales_remittance.setTimestamp("trans_date", new java.sql.Timestamp(trs.getTransDate().getTimeInMillis()));
            }else{
                call_put_trs_sales_remittance.setTimestamp("trans_date", null);
            }
            call_put_trs_sales_remittance.setString("sender_id", trs.getSenderId());
            call_put_trs_sales_remittance.setString("sender_name", trs.getSenderName());
            call_put_trs_sales_remittance.setString("sender_address", trs.getSenderAddress());
            call_put_trs_sales_remittance.setString("sender_city", trs.getSenderCity());
            call_put_trs_sales_remittance.setString("sender_phone", trs.getSenderPhone());
            call_put_trs_sales_remittance.setString("receiver_id", trs.getReceiverId());
            call_put_trs_sales_remittance.setString("receiver_name", trs.getReceiverName());
            call_put_trs_sales_remittance.setString("receiver_address", trs.getReceiverAddress());
            call_put_trs_sales_remittance.setString("receiver_city", trs.getReceiverCity());
            call_put_trs_sales_remittance.setString("receiver_phone", trs.getReceiverPhone());
            call_put_trs_sales_remittance.setFloat("trans_nominal", trs.getTransNominal());
            call_put_trs_sales_remittance.setFloat("trans_cost", trs.getTransCost());
            call_put_trs_sales_remittance.setFloat("trans_total", trs.getTransTotal());
            call_put_trs_sales_remittance.setFloat("trans_fee", trs.getTransFee());
            call_put_trs_sales_remittance.setFloat("trans_save", trs.getTransSave());
            call_put_trs_sales_remittance.setString("trans_reff", trs.getTransReff());
            call_put_trs_sales_remittance.setShort("is_refund", trs.getIsRefund());
            call_put_trs_sales_remittance.setInt("branch_id", trs.getBranchId());
            call_put_trs_sales_remittance.setInt("rekap_id", trs.getRekapId());
            call_put_trs_sales_remittance.addBatch();
        }
        call_put_trs_sales_remittance.executeBatch();
        return serverConn;
    }
}
